﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EasyInvoice.SqlCEDAL
{
    public class InvoiceDetail:IInvoiceDetail
    {
        //public EasyInvoice.Model.InvoiceDetail GetById(long id) { }

        public IEnumerable<EasyInvoice.Model.InvoiceDetail> GetAllByInvoiceId(long invoiceId)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@InvoiceId", invoiceId, System.Data.DbType.Int32, direction: ParameterDirection.Input);

                    string sql = string.Format("SELECT * FROM [InvoiceDetails] Where [InvoiceId] = @InvoiceId");

                    IEnumerable<EasyInvoice.Model.InvoiceDetail> InvoiceDetail = sqlConnection.Query<EasyInvoice.Model.InvoiceDetail>(sql, p, commandTimeout: 0, commandType: CommandType.Text);

                    sqlConnection.Close();
                    return InvoiceDetail;
                }
            }
            catch
            {
                throw;
            }
        }

        public long Save(EasyInvoice.Model.InvoiceDetail invoiceDetail)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", invoiceDetail.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    p.Add("@InvoiceId", invoiceDetail.InvoiceId, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    p.Add("@Description", invoiceDetail.Description, System.Data.DbType.String, direction: ParameterDirection.Input);
                    p.Add("@Price", invoiceDetail.Price, System.Data.DbType.Decimal, direction: ParameterDirection.Input);
                    p.Add("@Units", invoiceDetail.Units, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    string sql = string.Empty;
                    if (invoiceDetail.Id == 0)
                        sql = string.Format("INSERT INTO [InvoiceDetail] ([InvoiceId],[Description],[Price],[Units]) VALUES(@InvoiceId,@Description,@Price,@Units);");
                    else
                        sql = string.Format("UPDATE [InvoiceDetail] SET [InvoiceId] = @InvoiceId, [Description] = @Description, [Price] = @Price, [Units] = @Units WHERE [Id] = @Id");
                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);

                    if (invoiceDetail.Id == 0)
                    {
                        sql = string.Format("SELECT * FROM [InvoiceDetail] Where [Id] =  @@IDENTITY");
                        IEnumerable<EasyInvoice.Model.InvoiceDetail> details = sqlConnection.Query<EasyInvoice.Model.InvoiceDetail>(sql, p, commandType: CommandType.Text);
                        invoiceDetail.Id = details.First().Id;
                    }

                    sqlConnection.Close();
                    return invoiceDetail.Id;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public EasyInvoice.Model.InvoiceDetail Delete(EasyInvoice.Model.InvoiceDetail invoiceDetail)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", invoiceDetail.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);

                    string sql = string.Format("DELETE FROM [InvoiceDetail] WHERE [Id]=@Id;");
                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);

                    sqlConnection.Close();
                    return invoiceDetail;
                }
            }
            catch (Exception)
            {
                throw;
            }
           
        }
    }
}
